package com.share.common.utils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.share.common.common.DbSchemeColumn;
import com.share.common.enums.DbTypeEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import javax.sql.DataSource;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.Date;
import java.util.*;
import java.util.stream.Collectors;

/**
 * connection对象使用完毕后需要关闭。关掉后并不意味着就关闭了数据库连接池，
 * 这里的close()只是将数据库连接池中占用的connection释放掉，使其在连接池中处于空闲状态，
 * 若不关闭，数据库连接池中的connection中用完以后，请求就会处于队列状态，超出规定时间连接池就会将队列的请求断开，导致其无法进行连接。
 *
 * @author 01401061 - 孙艳强
 * @date 2020/10/30 14:19
 */
@Slf4j
public class DbUtil {

    /*获取连接*/
    public static Connection getConnection(String driver, String url, String user, String password) {
        try {
            //1、注册驱动
            Class.forName(driver);

            //2、获取连接对象
            return DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(url + "数据库连接获取失败");
        }
    }

    //Connection debug看到的属性与get的不一样，字段对应不上，很奇怪
    public static String getDbName(Connection con) {
        try {
            return con.getCatalog();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    //jdbc:mysql://127.0.0.1:3306/share
    public static String getDbName(String url) {
        String[] split = url.split("\\?");
        if (split != null) {
            String tmp = split[0];
            return tmp.substring(tmp.lastIndexOf("/") + 1);
        }
        return null;
    }

    public static JSONArray convert(ResultSet rs) {
        JSONArray jsonArray = new JSONArray();
        try {
            //获取列数
            int ColumnCount = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                JSONObject obj = new JSONObject();
                for (int i = 1; i <= ColumnCount; i++) {/*角标从1开始*/
                    String columnName = rs.getMetaData().getColumnLabel(i);
                    obj.put(columnName, rs.getObject(columnName));
                }
                jsonArray.add(obj);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return jsonArray;
    }


    public static List<DbSchemeColumn> showColumns(Connection conn, String dbName) throws Exception {
        String sql = "SELECT * FROM  INFORMATION_SCHEMA.COLUMNS #db# ORDER BY TABLE_NAME, ORDINAL_POSITION" ;
        if (dbName == null || dbName.length() == 0) {
            sql = sql.replaceAll("#db#", "");
        } else {
            sql = sql.replaceAll("#db#", "WHERE TABLE_SCHEMA = 'admin'");
        }
        PreparedStatement pstat = conn.prepareStatement(sql);
        ResultSet resultSet = pstat.executeQuery();
        List<DbSchemeColumn> list = convertToList(resultSet, DbSchemeColumn.class);
        return list;
    }

    //显示表的列---数据库会自动小写转大写
    public static List<String> showColumns(Connection conn, String tableName, String dbType) throws SQLException {
        String sql = null;
        if (DbTypeEnum.PHOENIX.code.equals(dbType)) {
            sql = "SELECT COLUMN_NAME FROM system.catalog WHERE table_name = '" + tableName.toUpperCase() + "'" ;
        } else {
            sql = "SHOW COLUMNS FROM " + tableName;
        }
        PreparedStatement pstat = conn.prepareStatement(sql);
        ResultSet resultSet = pstat.executeQuery();
        List<Map<String, Object>> list = convertToList(resultSet);

        List<String> rlist = null;
        if (DbTypeEnum.PHOENIX.code.equals(dbType)) {
            rlist = list.stream().map(m -> {
                String re = null;
                Object columnName = m.get("COLUMN_NAME");
                if (columnName != null) {
                    re = columnName.toString().trim();
                }
                return re;
            }).filter(e -> e != null && e.toString().length() > 0).collect(Collectors.toList());
        } else {
            rlist = list.stream().map(e -> e.get("id").toString()).collect(Collectors.toList());
        }
        return rlist;
    }

    public static Long insertOneRecord(Connection conn, String sql) throws SQLException {
        return insertOneRecord(conn, sql, true);
    }

    /**
     * 执行insert语句,返回主键id
     * nosql 不支持返回 主键id，也没有自增的sho
     *
     * @param conn
     * @param sql
     * @return
     * @throws SQLException
     */
    public static Long insertOneRecord(Connection conn, String sql, Boolean rId) throws SQLException {
        PreparedStatement pstat = null;
        if (rId != null && rId) {
            pstat = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        } else {
            pstat = conn.prepareStatement(sql);
        }

        int yingxiang = pstat.executeUpdate();//默认返回影响的记录数
        Long res = new Long(yingxiang);
        if (rId != null && rId) {
            ResultSet resultSet = pstat.getGeneratedKeys();
            resultSet.next();
            Long id = resultSet.getLong(1);//返回主键id
            res = id;
        }
        return res;
    }

    /**
     * 执行select语句,返回主键id
     *
     * @param conn
     * @param sql
     * @return
     * @throws SQLException
     */
    public static List<Map<String, Object>> selectRecord(Connection conn, String sql) throws SQLException {
        PreparedStatement pstat = conn.prepareStatement(sql);
        ResultSet resultSet = pstat.executeQuery();
        return convertToList(resultSet);
    }

    //输出只有一列的语句
    public static List<Object> selectOneColumn(Connection conn, String sql) throws SQLException {
        List<Object> relist = new ArrayList();
        PreparedStatement pstat = conn.prepareStatement(sql);
        ResultSet rs = pstat.executeQuery();
        while (rs.next()) {
            Object res = rs.getObject(1);//只要第一列
            relist.add(res);
        }
        return relist;
    }

    public static int saveAccessLog(DataSource dataSource, Map<String, Object> accessLog) {
        int re = 0;
        Connection conn = null;

        try {
            conn = dataSource.getConnection();
            String sql = toInsert("access_log", accessLog);
            re = insertOneRecord(conn, sql).intValue();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //连接必须关闭,否则会有很多问题
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return re;
    }

    /**
     * // 删除表中的数据 方便自己清理数据
     *
     * @param dataSource
     * @param tableNames
     * @param retainNum
     * @return
     */
    public static Map<String, Object> deleteDataById(DataSource dataSource, String[] tableNames, Integer retainNum) {
        Connection conn = null;
        Map<String, Object> reMap = null;
        try {
            conn = dataSource.getConnection();
            reMap = deleteDataById(conn, tableNames, retainNum);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //连接必须关闭,否则会有很多问题
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return reMap;
    }

    public static Map<String, Object> deleteDataById(Connection conn, String[] tableNames, Integer retainNum) {
        Map<String, Object> reMap = new HashMap<>();
        if (tableNames == null || tableNames.length < 1) {
            return reMap;
        }
        String tmpTemplate = "DELETE FROM #tableName WHERE id <= (SELECT * from (SELECT id from #tableName ORDER BY id DESC LIMIT 1 OFFSET #retainNum) t)" ;
        if (retainNum == null || retainNum <= 0) {
            tmpTemplate = "DELETE from #tableName" ;
        }
        final String template = tmpTemplate;

        Arrays.stream(tableNames).forEach(t -> {
            try {
                String sql = template.replaceAll("#tableName", t).replaceAll("#retainNum", "" + retainNum);
                log.info("清理数据sql={}", sql);
                Integer integer = deleteOrUpdate(conn, sql);

                reMap.put(t, integer);
            } catch (Exception e) {
                reMap.put(t, e.getMessage());
                e.printStackTrace();
            }
        });
        return reMap;
    }

    public static Integer deleteOrUpdate(Connection conn, String sql) throws SQLException {
        Statement statement = conn.createStatement();
        int yingxiang = statement.executeUpdate(sql);//默认返回影响的记录数
        return yingxiang;
    }

    public static List<Map<String, Object>> convertToList(ResultSet rs) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        ResultSetMetaData md = rs.getMetaData();// 获取键名
        int columnCount = md.getColumnCount();// 获取行的数量
        while (rs.next()) {
            Map<String, Object> rowData = new LinkedHashMap<>();// 声明Map----保持map的输入和输出顺序一致
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnName(i), rs.getObject(i));// 获取键名及值
            }
            list.add(rowData);
        }
        return list;
    }

    //结果集转对象
    public static <T> List<T> convertToList(ResultSet rs, Class<T> jClass) throws Exception {
        List<T> list = new ArrayList();
        ResultSetMetaData md = rs.getMetaData();// 获取键名
        int columnCount = md.getColumnCount();// 获取行的数量
        while (rs.next()) {
            T obj = jClass.newInstance(); // 声明jObj----保持jObj的输入和db的输出顺序一致
            for (int i = 1; i <= columnCount; i++) {
                String columnName = md.getColumnName(i);
                String setColumnName = StrUtil.linkSetName(columnName);
                Object value = rs.getObject(i);
                if (value == null) {
                    continue; //跳过空值
                }

                Method declaredMethod = jClass.getDeclaredMethod(setColumnName, value.getClass());
                declaredMethod.invoke(obj, value);
            }
            list.add(obj);
        }
        return list;
    }

    //INSERT INTO `dibike`.`action` (`id`, `actionID`, `actionCloumnID`, `bank3`) VALUES ('1', '1', '1', NULL);
    public static List<String> convertSelectToInsert(String tableName, ResultSet rs) {
        List<String> list = new LinkedList<>();

        try {
            ResultSetMetaData metaData = rs.getMetaData();
            int ColumnCount = metaData.getColumnCount(); //获取列数
            while (rs.next()) {
                StringBuffer sbColumn = new StringBuffer();
                sbColumn.append("insert INTO `" + tableName + "` (");
                StringBuffer sbValue = new StringBuffer();
                sbValue.append(" value (");
                for (int i = 1; i <= ColumnCount; i++) {/*角标从1开始*/
                    String columnName = metaData.getColumnLabel(i);
                    String columnType = metaData.getColumnTypeName(i);
                    int columnTypeLen = metaData.getColumnDisplaySize(i);
                    Object object = rs.getObject(columnName);

                    if (object != null) {/*跳过空值，所有类型都当做字符串处理*/
                        String value = object.toString();
                        if ("TINYINT".equals(columnType) && columnTypeLen == 1) {//这个数值类型特殊
                            value = "true".equals(value) ? "1" : "0" ;
                            log.info("TINYINT特殊列处理TINYINT类型tableName={},columnName={},列读取值={},列导出值={}", tableName, columnName, object, value);
                        } else if ("BIT".equals(columnType) && columnTypeLen == 1) {//这个数值类型特殊
                            value = "true".equals(value) ? "1" : "0" ;
                            log.info("BIT特殊列处理TINYINT类型tableName={},columnName={},列读取值={},列导出值={}", tableName, columnName, object, value);
                        }
                        sbColumn.append("`" + columnName + "`,");
                        sbValue.append("'" + value + "',");
                    }
                }
                //处理逗号
                if (sbColumn.charAt(sbColumn.length() - 1) == ',') {
                    sbColumn.deleteCharAt(sbColumn.length() - 1);
                }
                sbColumn.append(")");

                if (sbValue.charAt(sbValue.length() - 1) == ',') {
                    sbValue.deleteCharAt(sbValue.length() - 1);
                }
                sbValue.append(")");

                list.add(sbColumn.toString() + sbValue.toString() + ";" + System.lineSeparator());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static <T> List<T> convertBean(ResultSet rs, Class<T> t) {
        //创建一个JSONArray对象
        List<T> list = new LinkedList<>();

        try {
            //获取列数
            int ColumnCount = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                //创建ShowCreateTable用于存储解析的信息
                T obj = t.newInstance();

                for (int i = 1; i <= ColumnCount; i++) {/*角标从1开始*/
                    String columnName = rs.getMetaData().getColumnLabel(i);/*sql结果的列名*/
                    Object value = rs.getObject(columnName);/*获取该列的值*/
                    String standColumnName = columnName.replaceAll(" ", "").trim();//去掉所有的空格
                    String columnLowerCaseName = standColumnName.toLowerCase();
                    Class<?> paramType = Class.forName(rs.getMetaData().getColumnClassName(i));/*获取该列的值的数据类型*/
                    Method method = null;
                    try {
                        //eg  ColuMn_Name  --->coluMnName
                        String javaFieldTypeName = under2Camel(standColumnName);
                        String setMethodName = "set" + javaFieldTypeName.substring(0, 1).toUpperCase() + javaFieldTypeName.substring(1);
                        method = t.getMethod(setMethodName, paramType);
                    } catch (NoSuchMethodException e1) {
                        try {
                            //eg  ColuMn_Name  --->ColuMn_Name  (与数据库一致)
                            String set_Method_Name = "set" + standColumnName.substring(0, 1).toUpperCase() + standColumnName.substring(1);
                            method = t.getMethod(set_Method_Name, paramType);
                        } catch (NoSuchMethodException e2) {
                            try {
                                //eg  ColuMn_Name  --->colunMame  (忽略大小写的---强制小写)
                                String javaFieldLowerCaseName = under2Camel(columnLowerCaseName);
                                String setMethodLowerCaseName = "set" + javaFieldLowerCaseName.substring(0, 1).toUpperCase() + javaFieldLowerCaseName.substring(1);
                                method = t.getMethod(setMethodLowerCaseName, paramType);
                            } catch (NoSuchMethodException e3) {
                                log.warn("属性名与列名匹配不上!!!,请查看ShowTables类中是否有定义");
                            }
                        }
                    }
                    method.invoke(obj, value);
                }
                list.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public static String toInsert(String tableName, Object obj) {
        return toInsert(tableName, obj, null);
    }

    //批量插入其实更高效,所以这里就简单的写
    public static String toInsert(String tableName, Object obj, String dbType) {
        //参数校验
        if (obj == null) {
            return null;
        }
        List<Object> valueList = new LinkedList<>();
        valueList.add(obj);
        List<String> list = listToInsertBatch(tableName, valueList, dbType);
        if (list != null && list.size() >= 1) {
            return list.get(0);
        } else {
            return null;
        }
    }

    public static List<String> listToInsertBatch(String tableName, List<Object> param) {
        return listToInsertBatch(tableName, param, null);
    }

    //将list数据转换为批量insert语句
    public static List<String> listToInsertBatch(String tableName, List<Object> param, String dbType) {
        List<String> batchList = new LinkedList<>();

        //参数校验
        if (CollectionUtils.isEmpty(param)) {
            return batchList;
        }

        boolean hadExe = false;
        String headStr = null;
        Set<String> keySet = null;
        StringBuffer tmpSb = new StringBuffer();
        List<String> valueList = new LinkedList<>();
        for (Object obj : param) {
            Object tmp = obj;
            if (!(obj instanceof JSONObject)) {
                tmp = JSON.parseObject(JSON.toJSONString(obj));
            }
            Map<String, Object> columnMap = (Map<String, Object>) tmp;

            tmpSb.setLength(0);//清空
            if (!hadExe) {
                //头部计算只需要计算一次
                hadExe = true;

                keySet = new TreeSet(columnMap.keySet());
                keySet.forEach(e -> {
                    if (DbTypeEnum.PHOENIX.code.equals(dbType)) {
                        tmpSb.append("," + e);
                    } else {
                        tmpSb.append(",`" + e + "`");
                    }
                });
                tmpSb.deleteCharAt(0);
                tmpSb.append(")");
                if (DbTypeEnum.PHOENIX.code.equals(dbType)) {
                    tmpSb.insert(0, "upsert into " + tableName + " (");
                } else {
                    tmpSb.insert(0, "insert into " + tableName + " (");
                }

                headStr = tmpSb.toString();
            }

            //数据部分
            tmpSb.setLength(0);//清空
            keySet.forEach(e -> {
                Object value = columnMap.get(e);
                if (value == null || StringUtils.isBlank(value.toString())) {
                    tmpSb.append(",NULL");
                } else {
                    String strValue = value.toString();
                    if (value instanceof Date) { //类型适配
                        strValue = DateUtil.getDateTime((Date) value);
                    }
                    if ("id".equals(e) && DbTypeEnum.PHOENIX.code.equals(dbType) && strValue.contains("NEXT ")) {
                        //没有自增id只能用序列
                        tmpSb.append("," + strValue);
                    } else {
                        tmpSb.append(",'" + strValue + "'");
                    }
                }
            });
            tmpSb.deleteCharAt(0);
            tmpSb.append(")");
            tmpSb.insert(0, "(");
            String dataStr = tmpSb.toString();

            //暂存所有的value
            valueList.add(dataStr);
        }

        //对数据进行切割
        final int BATCH_SIZE = 1000;
        List<List<String>> split = CollectionUtil.splitList(valueList, BATCH_SIZE);
        for (List<String> tmp1 : split) {
            tmpSb.setLength(0);//清空
            if (tmp1 == null || tmp1.size() < 1) {
                continue;
            }

            for (String gList : tmp1) {
                tmpSb.append("," + gList);
            }
            tmpSb.deleteCharAt(0);

            batchList.add(headStr + " values " + tmpSb.toString());
        }

        return batchList;
    }

    private static String under2Camel(String standColumnName) {
        String tmp = StrUtil.under2Camel(standColumnName);
        return tmp.substring(0, 1).toLowerCase() + tmp.substring(1);
    }

    /**
     * CREATE TABLE IF NOT EXISTS test_hbase (
     * id INTEGER PRIMARY KEY,
     * name VARCHAR,
     * age INTEGER
     * );
     *
     * @param tableName
     * @param object
     * @return
     */
    public static String toHbaseCreateTable(String tableName, Object object) {
        String createSql = null;
        String tmp = "CREATE TABLE IF NOT EXISTS #tableName# (" + System.lineSeparator() + "\tid INTEGER PRIMARY KEY," + System.lineSeparator() + "#column#" + ")" ;

        String jsonStr = JSON.toJSONString(object, SerializerFeature.WriteMapNullValue);
        Map<String, Object> map = JSON.parseObject(jsonStr, Map.class);
        Set<String> sets = map.keySet();
        sets.remove("id");
        StringBuffer column = new StringBuffer();
        for (String str : sets) {
            column.append("\t" + str + " VARCHAR," + System.lineSeparator());
        }

        if (column.length() > 2) {
            column.deleteCharAt(column.lastIndexOf(","));
            createSql = tmp.replace("#column#", column.toString()).replace("#tableName#", tableName);
        }
        return createSql;
    }

    /**
     * CREATE TABLE `config_info` (
     * `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
     * `data_id` varchar(255),
     * PRIMARY KEY (`id`)
     * )
     */
    public static String toMysqlCreateTable(String tableName, Object object) {
        String createSql = null;
        String tmp = "CREATE TABLE IF NOT EXISTS `#tableName#` (" + System.lineSeparator() + "\t`id` bigint NOT NULL AUTO_INCREMENT," + System.lineSeparator() + "#column#" + "\tPRIMARY KEY (`id`)" + System.lineSeparator() + ")" ;

        String jsonStr = JSON.toJSONString(object, SerializerFeature.WriteMapNullValue);
        Map<String, Object> map = JSON.parseObject(jsonStr, Map.class);
        Set<String> sets = map.keySet();
        sets.remove("id");
        StringBuffer column = new StringBuffer();
        for (String str : sets) {
            column.append("\t`" + str + "` varchar(255)," + System.lineSeparator());
        }

        if (column.length() > 2) {
            createSql = tmp.replace("#column#", column.toString()).replace("#tableName#", tableName);
        }
        return createSql;
    }

    public static List<String> showTables(Connection connection) {
        return showTables(connection, DbTypeEnum.MYSQL.code);
    }

    //TABLE_SCHEM | TABLE_NAME |  TABLE_TYPE  | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE_ROWS | SALT_BUCK |
    //+-----------+-------------+------------+--------------+---------+-----------+---------------------------+----------------+-------------+----------------+-----------+
    //|           | SYSTEM      | CATALOG    | SYSTEM TABLE

    // Table_in_json2flat
    // jf_mapping_conf
    public static List<String> showTables(Connection con, String dbType) {
        List<String> reList = new LinkedList<>();
        try {
            PreparedStatement ps = con.prepareStatement("show  tables");
            ResultSet rs = ps.executeQuery();
            List<Map<String, Object>> list = convertToList(rs);
            if (DbTypeEnum.PHOENIX.code.equals(dbType)) {
                //1、发送SQL语句，返回结果集
                for (Map<String, Object> map : list) {
                    String tableType = (String) map.get("TABLE_TYPE");
                    if ("SYSTEM TABLE".equals(tableType)) {
                        continue;
                    }

                    String TABLE_NAME = (String) map.get("TABLE_NAME");
                    if (StringUtils.isNotBlank(TABLE_NAME)) {
                        String TABLE_SCHEM = (String) map.get("TABLE_SCHEM");
                        if (StringUtils.isEmpty(TABLE_SCHEM)) {
                            TABLE_SCHEM = "" ;
                        } else {
                            TABLE_SCHEM += "." ;
                        }
                        reList.add(TABLE_SCHEM + TABLE_NAME);
                    }
                }
            } else {
                for (Map<String, Object> map : list) {
                    Collection<Object> values = map.values();
                    if (!CollectionUtils.isEmpty(values)) {
                        Object t = values.stream().findFirst().get();
                        if (t != null && t.toString().length() > 0) {
                            reList.add(t.toString());
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return reList;
    }
}
